MySQL深入了解与性能优化

最新推荐文章于 2024-08-23 22:22:04 发布

我是陈旭原

最新推荐文章于 2024-08-23 22:22:04 发布

阅读量693

点赞数

本文链接：https://blog.csdn.net/suchahaerkang/article/details/84031112

版权

索引优化

索引创建

CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));

索引删除

DROP INDEX [indexName] ON mytable;

查看索引

SHOW INDEX FROM mytable;

优势：

1.类似图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本

2.通过索引对数据进行排序，降低数据库的排序成本，降低了CPU的消耗

劣势：

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是占用空间的。
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT,UPDATE,DELETE.因为更新表时，MYSQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新带来的键值变化后的索引信息。
索引只是提高效率的一个因素，如果你的MYSQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询。

哪些情况需要创建索引：

1.主键自动建立唯一索引

2.频繁作为查询条件的字段应该创建索引

3.查询中与其它表关联的字段，外键关系建立索引

4.频繁更新的字段不适合建索引

5.where条件里用不到的的字段不创建索引

6.单键/组合索引的选择问题（在高并发的下倾向创建组合索引）

7.查询中排序的字段，排序的字段若通过索引区访问，将大大提高排序速度

8.查询中统计或者分组的字段

哪些情况不需要建索引：

1.表的记录太少

2.经常增删改的字段

3.数据重复且分布平均的表字段

多表连接—Join 语句优化：

1.尽可能减少join 语句中的NestedLoop的循环总次数，永远用小结果集驱动大结果集。

2.优先优化NestedLoop的内存循环。

3.保证Join语句中被驱动表上的Join条件字段已经被索引。

4. 当无法保证被驱动表的Join条件字段被索引，且内存充足的情况下适当加大JoinBuffer大小的设置。

建索引的建议：

对于单键索引，尽量选择针对当前query过滤性更好的索引；
在选择组合索引的时候，当前的query中过滤性最好的字段在索引的字段顺序中位置越靠前越好；
在选择组合索引的时候，尽量选择可以能够包含当前query中where子句中更多字段的索引；
尽可能通过分析统计信息和调整query的写法来达到选择合适的索引的目的。

Order by优化：

Mysql支持两种方式的排序：FileSort和Index，Index效率高。Sql扫描索引本身完成排序，FileSort效率较低。

1.Order by子句，尽量使用index方式排序，避免使用fileSort方式排序

2.尽可能在索引列上完成排序操作，遵循索引建的最佳左前缀

3.如果不在索引列上，filesort有两种算法：双路排序和单路排序

双路排序：

Mysql 4.1 之前使用的是双路排序，字面意思是进行两次扫描磁盘，最终得到数据。

读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

从磁盘取排序字段，在buffer进行排序，再从磁盘取其它字段。

单路排序:

Mysql 4.1之后，出现了第二种改进算法，就是单路排序。

从磁盘读取查询需要的所有的列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是他会使用更多的磁盘空间，因为他把每一行都保存在内存中了。

注意：

因为单路排序是一次把所有字段取出，所以可能取出数据的总大小超出sort_buffer的容量，导致没次只能取出sort_buffer容量大小的数据，进行排序（创建tmp文件多路合并），拍完序再取出sort_buffer容量大小，再排，从而导致多次IO。

优化策略：

增大sort_buffer_size参数的设置

增大max_length_for_sort_data参数的设置

增高oredr by的速度：

Order by 时不要使用select * ，只select出需要的字段，在这里的影响是：
1. 当Query的字段的大小总和小于max_length_for_sort_data而且排序字段不是TEXT/BLOB类型时，会用改进后的算法-单路排序，否则用老算法-多路排序。
2. 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，会导致多次IO，但是用单路排序算法的风险更大一些，所以要提高sort_buffer_size。
尝试提高sort_buffer_size

不管用哪种算法，提高这个参数都会提高效率，当然要根据系统的能力去提高，因为这个参数是针对每个进程的。

尝试提高max_length_for_sort_data

提高这个参数，会增加使用改进算法的概率。如果没有设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状的磁盘IO活动和低的处理器使用率。

为排序使用索引

Mysql两种排序方式：文件排序或扫描有序索引排序

Mysql能为排序与查询使用相同的索引

Group by关键字的优化：

group by分组之前必排序。

Group by的实质是先排序，后进行分组，按照索引建的最佳左前缀
当无法使用索引列，增大sort_buffer_size、max_length_for_sort_data参数的设置
Where高于having，能写在where子句下的限定条件就不要写在having下了。

为什么用B/B+树（平衡多路查找树）这种结构来实现索引呢？？

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来

局部性原理与磁盘预读:

实际过程中，磁盘并不是每次严格按需读取，而是每次都会预读。磁盘读取完需要的数据后，会按顺序再多读一部分数据到内存中，这样做的理论依据是计算机科学中注明的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用

程序运行期间所需要的数据通常比较集中

mysql是基于磁盘的数据库，索引是以索引文件的形式存在于磁盘中的，索引的查找过程就会涉及到磁盘IO消耗，磁盘IO的消耗相比较于内存IO的消耗要高好几个数量级，所以索引的组织结构要设计得在查找关键字时要尽量减少磁盘IO的次数。

（1）由于磁盘顺序读取的效率很高(不需要寻道时间，只需很少的旋转时间)，

因此对于具有局部性的程序来说，预读可以提高I/O效率.预读的长度一般为页(page)的整倍数。

（2）MySQL(默认使用InnoDB引擎),将记录按照页的方式进行管理,每页大小默认为16K(这个值可以修改)。linux 默认页大小为4K。

B-Tree借助计算机磁盘预读的机制，并使用如下技巧：
每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个结点只需一次I/O。

假设 B-Tree 的高度为 h, B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)O(h)=O(logdN)。

一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3，也即索引的B+树层次一般不超过三层，所以查找效率很高）。

一棵高度为2的B+树，大概可以存放：2w条大小为1k的记录

一个高度为3的B+树大概可以存放：2000w条大小为1k的记录

为什么mysql的索引使用B+树而不是B树呢？？
（1）B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。
（2）mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。

Explain

--建表

CREATE TABLE `emp` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`dept_id` int(11),

`name` varchar(30),

`sex` char,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `dept` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`dept_name` varchar(30),

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `dept_location` (

`dept_id` int(11) NOT NULL AUTO_INCREMENT,

`location` varchar(30),

PRIMARY KEY (`dept_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `adress` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`emp_id` int(11),

`address` varchar(30),

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into emp(dept_id,name,sex) values(1,'Jane','0');

insert into emp(dept_id,name,sex) values(2,'Mary','0');

insert into emp(dept_id,name,sex) values(1,'Jack','1');

insert into emp(dept_id,name,sex) values(3,'Leo','0');

insert into emp(dept_id,name,sex) values(1,'Ruby','0');

insert into dept(dept_name) values('Business');

insert into dept(dept_name) values('Market');

insert into dept(dept_name) values('Software');

insert into adress(emp_id,address) values(1,'深圳');

insert into adress(emp_id,address) values(2,'深圳');

insert into adress(emp_id,address) values(3,'深圳');

insert into adress(emp_id,address) values(4,'北京');

insert into adress(emp_id,address) values(5,'北京');

Select_type:

explain select emp.name,(select dept.dept_name from dept where dept.id = emp.dept_id) from emp;

explain select d1.name,(select id from adress) d2

from (select id,name from emp where sex ='0') d1

where d1.name ='Mary'

union

(select id,dept_name from dept);

Type:

explain select * from emp;

explain select * from emp where id = 1;

--eq_ref

explain select * from dept,dept_location where dept.id = dept_location.dept_id;

--ref 非唯一性索引扫描

create index idx_emp_name on emp(name);

explain select * from emp where name ='Mary';

--index

explain select id from emp;

--possible_keys key

explain select dept.*

from emp,dept,dept_location

where emp.id = dept.id and dept.id = dept_location.dept_id

and dept.dept_name = 'Business';

覆盖索引

create index idx_emp_ns on emp(name,sex);

explain select name ,sex from emp;

-- key_len

列类型	KEY_LEN	备注
id int	key_len = 4+1	int为4bytes,允许为NULL,加1byte
id bigint not null	key_len=8	bigint为8bytes
user char(30) utf8	key_len=30*3+1	utf8每个字符为3bytes,允许为NULL,加1byte
user varchar(30) not null utf8	key_len=30*3+2	utf8每个字符为3bytes,变长数据类型,加2bytes
user varchar(30) utf8	key_len=30*3+2+1	utf8每个字符为3bytes,允许为NULL,加1byte,变长数据类型,加2bytes
detail text(10) utf8	key_len=30*3+2+1	TEXT截取部分,被视为动态列类型。

说明：索引字段的附加信息:可以分为变长和定长数据类型讨论，当索引字段为定长数据类型时,如char，int，datetime,需要有是否为空的标记,这个标记占用1个字节(对于not null的字段来说,则不需要这1字节);对于变长数据类型,比如varchar,除了是否为空的标记外,还需要有长度信息,需要占用两个字节。

对于,char、varchar、blob、text等字符集来说，key len的长度还和字符集有关,latin1一个字符占用1个字节,gbk一个字符占用2个字节,utf8一个字符占用3个字节。

character set：utf8=3,gbk=2,latin1=1 len = 3

key_len的长度计算公式：

varchr(10)变长字段且允许NULL = 10 * 3 + 1(NULL) + 2(变长字段)

varchr(10)变长字段且不允许NULL = 10 * 3 + 2(变长字段)

char(10)固定字段且允许NULL = 10 * 3 + 1(NULL)

char(10)固定字段且不允许NULL = 10 * 3

Explain select * from dept , dept_location

where dept.id = dept_location.dept_id

and dept_location.location ='ac';

--建索引优化

create index idx_dl_location on dept_location(location);

Explain select * from dept , dept_location

where dept.id = dept_location.dept_id

and dept_location.location ='ac';

Extra:

CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`col2` int(11),

`col3` varchar(30),

`col4` char,

`col5` varchar(30),

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

create index idx_t1_234 on t1(col2,col3,col4);

--using filesort

explain select col2 from t1 where col2 =5 order by col4;

--优化

explain select col2 from t1 where col2 =5 order by col3,col4;

--using temporary

explain select col3 from t1 where col2>2 group by col3 \G;

explain select col3 from t1 where col2>2 group by col2,col3 \G;

慢查询日志：

Mysql提供的一种日志记录，它用来记录mysql中响应时间超过阈值（long_query_time）的语句.

long_query_time的默认值为10，即记录运行10s以上的语句。

开启MySQL的慢查询日志功能
默认情况下，MySQL是没有开启慢查询日志的。要开启这个功能，

Show variables like ‘%slow_query_log%’;

默认10s

开启：

Set global slow_query_log = 1

只对当前的数据库生效，如果mysql重启之后会失效。

show global status like ‘%Slow_queries%’; 查询当前系统有哪些慢查询sql

如果要永久生效：

我们需要修改MySQL的配置文件，windows下修改my.ini,Linux下修改my.cnf文件,在[mysqld]最后增加如下命令：

slow_query_log
long_query_time = 1

slow_query_log_file = filepath

最后一个参数指定慢查询日志的文件存放路径，没有指定的话，系统默认会给一个缺省的文件：
host_name-slow.log

*如果不是调优需要，一般不建议启动该参数，因为开启慢查询日志或多或少会带来一定的性能影响*

Mysql提供的日志分析工具：mysqldumpslow

如：mysqldumpslow -s r -t 10 /var/lib/,ysql/slow.log

Show Profile:

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于sql的调优的测量。

默认情况下参数处于关闭状态，并保存最近15次的运行结果。

https://dev.mysql.com/doc/refman/5.5/en/show-profile.html

show variables like '%profiling%';

set profiling = on;

运行sql

开启之后，查询的sql就会被记录；

查看结果

Show profiles;

诊断sql：

Show profile cpu, block io for query <查询的id>;

比较耗内存/时间的四个操作：

Converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬。

Creating tmp table创建临时表

Coping to tmp table on disc 把内存中临时表复制到磁盘

locked

全局查询日志：
set global general_log = 1;

set global log_output = ‘table’;

此后，查询的sql都会记录在mysql数据库里的general_log表，可以用下面的命令查看：

Select * from mysql.general_log;

**永远不要在生产环境开启这个功能。

二、InnoDB锁

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。

数据库事务隔离级别：

在并发事务处理带来的问题中，“更新丢失”通常应该是完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。

“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以分为以下两种。

READ UNCOMMITTED（未提交读）

在这种隔离级别下，事物中的修改，即使没有提交，对其他失误也都是可见的。

事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。一般在实际应用中很少使用。

READ COMMITTED（提交读）

一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始到提交之前，所做的任何修改对其它事务都是不可见的。这个级别也叫做不可重复读，因为在一个事务中两次执行同样的查询，可能会得到不一样的结果。大多数数据库系统默认的隔离级别都是READ COMMITTED（但mysql不是）。

REPEATABLE READ（可重复读）

解决了脏读和不可重复读的问题，保证了在同一个事务当中多次读取同样的记录的结果是一致的。但是可能存在幻读的问题。InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC）解决了幻读的问题。可重复读是mysql的默认事务隔离级别。

SERIALIZATION（可串行化）

是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。SERIALIZATION会在读取的每一行的数据上都加上锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只要在非常需要保证数据一致性的情况而且接收没有并发的情况下，才考虑该级别。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上 “串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4个事务隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同，应用可以根据自己的业务逻辑要求，通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾。下表很好地概括了这4个隔离级别的特性。

InnoDB的行锁模式及加锁方法

-- 加锁

lock table mylock read, class write;

-- 解锁

unlock tables;

读锁： 当前session对锁定表的查询操作可以执行，对其它表的任何操作都不可执行

其它session对当前表的查询操作可以执行，对当前表的更新操作不可以执行

写锁：当前session对锁定表的查询+更新+插入操作都可以执行，对其它表的任何操作都不可执行；

其它session对锁定表的查询被阻塞，需要等待锁被释放。

总结： 读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都堵塞。

间隙锁：

当我们使用范围条件而不是相等条件检索数据，并请求共享锁或排他锁时，InnoDB 会给复合条件的已有数据记录的索引项加锁；对于在条件范围内但是不存在的记录叫做“间隙（GAP）”，

InnoDB也会给这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

影响：

因为Query执行过程中，通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键不存在。在锁定的过程中，无法往锁定范围内插入任何数据。

GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。确实，GAP锁锁住的位置，也不是记录本身，而是两条记录之间的GAP。所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。

如何保证两次当前读返回一致的记录，那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能，GAP锁应运而生。

如何分析行锁定：

通过检查InnoDb_row_lock状态变量来分析系统上的行锁的争夺情况。

Show status like 'Innodb_row_lock%';

Innodb_row_lock_current_waits: 当前正在等待锁定的数量；

Innodb_row_lock_time: 从系统启动到现在，锁定时间的总长度；

Innodb_row_lock_time_avg：每次等待所花的平均时间；

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间；

Innodb_row_lock_waits：从系统启动到现在总共等待的次数。

优化建议：

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁；
合理设计索引，尽量缩小锁的范围
尽可能较少的索引条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能降低事务的隔离级别

CREATE TABLE `mylock` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(30),

PRIMARY KEY (`id`)

) ENGINE=MyISAM;

insert into mylock(name) values('a');

insert into mylock(name) values('b');

insert into mylock(name) values('c');

insert into mylock(name) values('d');

insert into mylock(name) values('e');

insert into mylock(name) values('f');

insert into mylock(name) values('g');

--读锁

lock table mylock read;

select * from mylock;

Session 1

update mylock set name ='s' where id=1;

Session 2 --读阻塞写

update mylock set name ='s' where id=1;

Session1释放读锁，session2写成功

--写锁：

间隙锁：

CREATE TABLE `innodb_lock` (

`id` int(11) NOT NULL,

`name` varchar(30),

PRIMARY KEY (`id`)

) ENGINE=INNODB;

insert into innodb_lock values(1,'a');

insert into innodb_lock values(3,'b');

insert into innodb_lock values(4,'c');

insert into innodb_lock values(5,'d');

insert into innodb_lock values(6,'e');

insert into innodb_lock values(7,'f');

insert into innodb_lock values(8,'g');

--session 1

Set auto_commit = 0;

update innodb_lock set name ='update' where id>1 and id<4;

--session 2

insert into innodb_lock values(2,'b');
--session 1

Commit;

Id为2 的记录虽然不存在，但是这行间隙也会被锁住。

三、主从复制：

master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
Slave将binary log events拷贝到它的中继日志（relay log）；
Slave重做中继日志中的事件，将改变应用到自己的数据库中，MYSQL的复制是异步的且串行化的。

主从复制解决的问题：

数据分布

可以随意的停止或开始复制，并在不同的地理位置来分布数据备份，例如不同的数据中心。

负载均衡
可以将读操作分布到多个服务器上，实现对读密集型应用的优化。（DNS轮询，网络负载均衡...）
备份
高可用和故障切换
能够帮助应用程序避免MYSQL单点失败，发生故障时可以通过切换数据库缩短宕机时间。
Mysql升级测试
使用一个更高版本的MYSQL作为备库，保证在升级全部实例前，查询能够在备库按照预期执行。

原理：

基于语句的复制：

在mysql 5.0及之前的版本中只支持基于语句的复制（也称为逻辑复制），基于语句复制的模式下，主库会记录那些造成数据更改的查询，当备库读取到并重放这些事件的时候，实际上是把主库上执行过得sql再执行一遍。

基于行的复制

mysql 5.1开始支持基于行的复制，这种方式会将实际数据记录在二进制日志中。

复制的基本原则：

每个Slave只有一个master；

每个Slave只能有一个唯一的服务器id；

每个master可以有多个Slave。

一主一从最常见。

主从复制配置过程：

主节点：

启用二进制日志。
为当前节点设置一个全局唯一的server_id。
创建有复制权限的用户账号 REPLIACTION SLAVE ,REPLIATION CLIENT。

从节点：

启动中继日志。
为当前节点设置一个全局唯一的server_id。
使用有复制权限的用户账号连接至主节点，并启动复制线程。

分库、分表：

当用户量级上来后，写请求越来越多，该怎么办？加一个Master是不能解决问题的，因为数据要保存一致性，写操作需要2个master之间同步，相当于是重复了，而且更加复杂。

这时就需要用到分库分表（sharding），对写操作进行切分。

用户请求量太大

因为单服务器TPS，内存，IO都是有限的。

解决方法：分散请求到多个服务器上；其实用户请求和执行一个sql查询是本质是一样的，都是请求一个资源，只是用户请求还会经过网关，路由，http服务器等。

单库太大

单个数据库处理能力有限；单库所在服务器上磁盘空间不足；

单库上操作的IO瓶颈解决方法：切分成更多更小的库

单表太大

CRUD都成问题；索引膨胀，查询超时

解决方法：切分成多个数据集更小的表。

分库分表的方式方法：

一般就是垂直切分和水平切分，这是一种结果集描述的切分方式，是物理空间上的切分。

然后是单个库太大，这时我们要看是因为表多而导致数据多，还是因为单张表里面的数据多。

分库：如果是因为表多而数据多，使用垂直切分，根据业务切分成不同的库。

分表: 如果是因为单张表的数据量太大，这时要用水平切分，即把表的数据按某种规则切分成多张表，甚至多个库上的多张表。

分库分表的顺序应该是先垂直分，后水平分。

垂直拆分

垂直分表

也就是“大表拆小表”，基于列字段进行的。一般是表中的字段较多，将不常用的，数据较大，长度较长（比如text类型字段）的拆分到“扩展表“。一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。

垂直分库

垂直分库针对的是一个系统中的不同业务进行拆分，比如用户User一个库，商品Producet一个库，订单Order一个库。切分后，要放在多个服务器上，而不是一个服务器上。为什么？我们想象一下，一个购物网站对外提供服务，会有用户，商品，订单等的CRUD。没拆分之前，全部都是落到单一的库上的，这会让数据库的单库处理能力成为瓶颈。按垂直分库后，如果还是放在一个数据库服务器上，随着用户量增大，这会让单个数据库的处理能力成为瓶颈，还有单个服务器的磁盘空间，内存，tps等非常吃紧。所以我们要拆分到多个服务器上，这样上面的问题都解决了，以后也不会面对单机资源问题。

数据库业务层面的拆分，和服务的“治理”，“降级”机制类似，也能对不同业务的数据分别的进行管理，维护，监控，扩展等。数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

水平拆分

水平分表

针对数据量巨大的单张表（比如订单表），按照某种规则（RANGE,HASH取模等），切分到多张表里面去。但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。不建议采用。

水平分库分表

将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。

水平分库分表切分规则